package dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Enumeration;
import java.util.Hashtable;

import vo.Status;
import vo.User;
import JDBC.DatabaseDao;
import Service.StatusService;
import Utils.MathUtil;

public class UserDao {

	static Connection con = DatabaseDao.getConnection();

	/**
	 * @author huangshuigui 对该用户userId，获取该用户的所有的评论，对每条评论获取词语长度，再求平均
	 * @param userId
	 *            : 用户标识
	 * @return double: 用户所有评论的平均词语个数
	 * 
	 *         TODO
	 */
	public static double getAvgCommentWordCount(String userId) {
		double result = 0;
		String sql = String.format("select comment_id from comments where user_id=%s", userId);
		try {
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			int num = 0;
			while (rs.next()) {
				String commentId = rs.getBigDecimal("comment_id") + "";
				result += CommentDao.getWordCount(commentId);
				num++;
			}
			if (num == 0) {
				System.out.println("no comments for comment_id = " + userId);
			} else {
				result = result / num;
				System.out.println("total comments: " + num + " average word count :" + result);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}

	/**
	 * @author huangshuigui 对该用户userId，获取该用户的所有的状态，对每条状态获取词语长度，再求平均
	 * @param userId
	 *            : 用户标识
	 * @return double: 用户所有状态的平均词语个数
	 * 
	 *         TODO
	 */
	public static double getAvgStatusWordCount(String userId) {
		double result = 0;
		String sql = String.format("select content from statuses where user_id=%s", userId);
		try {
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			int num = 0;
			while (rs.next()) {
				String content = rs.getString("content");
				result += StatusDao.getWordCount(content);
				num++;
			}
			if (num == 0) {
				System.out.println("no statuses for user_id = " + userId);
			} else {
				result = result / num;
				System.out.println("total statuses: " + num + " average word count :" + result);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}

	public static String initCountCondition(User user) {
		String result = "select count(*) from users";
		if (user == null)
			return result;

		boolean firstCondition = true;
		result += " where ";
		if (user.getUserId() != null && !user.getUserId().equals("")) {
			result += "user_id='" + user.getUserId() + "'";
			firstCondition = false;
		}
		if (user.getGender() != null && !user.getGender().equals("")) {
			if (firstCondition == false)
				result += " and ";
			result += "gender='" + user.getGender() + "'";
			firstCondition = false;
		}
		if (user.getVerified() != -1) {
			if (firstCondition == false)
				result += " and ";
			result += "verified=" + user.getVerified();
		}
		return result;

	}

	/**
	 * 查找符合user属性条件的用户个数
	 * 
	 * @author 韩 闻文
	 * @return
	 */
	public static int getCountByCondition(User user) {
		int result = 0;
		try {
			String sql = initCountCondition(user);
			System.out.println(sql);
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			rs.next();
			result = rs.getInt(1);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}

	/**
	 * 获得所有的用户列表
	 * 
	 * @author 韩 闻文
	 * @return
	 */
	public static ArrayList<User> getAll() {
		ArrayList<User> result = new ArrayList<User>();
		try {
			String sql = "select * from users";
			System.out.println(sql);
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				User tmp = new User();
				// 基本信息
				tmp.setUserId(rs.getString("user_id"));
				tmp.setGender(rs.getString("gender"));
				tmp.setName(rs.getString("name"));
				tmp.setScreenName(rs.getString("screen_name"));
				tmp.setDescription(rs.getString("description"));
				tmp.setVerified(rs.getInt("verified"));
				tmp.setCreatedAt(rs.getString("created_at"));
				// 地点信息
				tmp.setProvince(rs.getString("province"));
				tmp.setCity(rs.getString("city"));
				tmp.setLocation(rs.getString("location"));
				// 统计信息
				tmp.setFollowsCount(rs.getInt("followers_count"));
				tmp.setStatusesCount(rs.getInt("statuses_count"));
				tmp.setFriendCount(rs.getInt("friends_count"));
				result.add(tmp);

			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}

	/**
	 * @author 韩 闻文
	 * @return
	 */
	public static ArrayList<String> getAllIds() {
		ArrayList<String> userIds = new ArrayList<String>();
		try {
			String sql = "select * from users";
			// System.out.println(sql);
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				userIds.add(rs.getString("user_id"));
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return userIds;
	}

	/**
	 * @author 韩 闻文
	 * @return
	 */
	public static ArrayList<Integer> getAllFollowersCounts() {
		ArrayList<Integer> followersCounts = new ArrayList<Integer>();
		try {
			String sql = "select followers_count from users";
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				followersCounts.add(rs.getInt("followers_count"));
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return followersCounts;
	}

	/**
	 * 用户的评论的平均数
	 * 
	 * @author 韩 闻文
	 * @return
	 */
	public static double getAVGCommentsCount() {
		double sum = 0.0;
		int count = 0;
		try {
			String sql = "select count(*) from comments group by user_id";
			System.out.println(sql);
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				sum += rs.getInt(1);
				count++;
			}

		} catch (Exception e) {
			e.printStackTrace();
		}
		return (double) sum / count;
	}

	/**
	 * 用户帖子被转发的平均数
	 * 
	 * @author 韩 闻文
	 * @return
	 */
	public static double getAVGRetweetsCount() {
		double sum = 0.0;
		ArrayList<User> users = UserDao.getAll();
		for (int i = 0; i < users.size(); i++) {
			String userId = users.get(i).getUserId();
			sum += UserDao.getRetweetCountByUserId(userId);
		}
		return (double) sum / users.size();
	}

	/**
	 * 用户的总转发数最大值
	 * 
	 * @author 韩 闻文
	 * @return
	 */
	public static int getMAXRetweetsCount() {
		int result = Integer.MIN_VALUE;
		ArrayList<User> users = UserDao.getAll();
		for (int i = 0; i < users.size(); i++) {
			String userId = users.get(i).getUserId();
			int count = UserDao.getRetweetCountByUserId(userId);
			if (count > result)
				result = count;
		}
		return result;
	}

	/**
	 * @author 韩 闻文
	 * @return
	 */
	public static double getMAXCommentsCount() {
		double result = Double.MIN_VALUE;
		try {
			String sql = "select count(*) from comments group by user_id";
			System.out.println(sql);
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				if (rs.getInt(1) > result)
					result = rs.getInt(1);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}

	/**
	 * @author 韩 闻文 get count of users by different kinds of users
	 * @return
	 */
	public static double getVarianceByCondition(String attribute) {
		ArrayList<Integer> counts = new ArrayList<Integer>();
		double result = 0;
		try {
			String sql = "select " + attribute + " from users";
			System.out.println(sql);
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				counts.add(rs.getInt(attribute));
			}
			result = MathUtil.variance(counts);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}

	/**
	 * @author 韩 闻文 get count of users by different kinds of users
	 * @return
	 */
	public static long getAVGByCondition(String attribute) {
		long result = 0;
		try {
			String sql = " select  avg(cast(" + attribute + "  as  bigint))   from   users";
			System.out.println(sql);
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			rs.next();
			result = rs.getLong(1);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}

	/**
	 * @author 韩 闻文 get count of users by different kinds of users
	 * @return
	 */
	public static int getMAXByCondition(String attribute) {
		int result = 0;
		try {
			String sql = "select max(" + attribute + ") from users";
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			rs.next();
			result = rs.getInt(1);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}

	/**
	 * @author 韩 闻文 the first and last user register time
	 * @return
	 */
	public static String getSysJoinTimeRange() {
		String result = "first one -last one\n";
		try {
			String sql = "select min(created_at),max(created_at) from users";
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			rs.next();
			result += rs.getString(1) + ",";
			result += rs.getString(2) + "\n";
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}

	/**
	 * @author 韩 闻文 get user's status count
	 * @param userId
	 * @return
	 */
	public static int getStatusCountByUserId(String userId) {
		int result = 0;
		Statement stmt = null;
		try {
			String sql = "select statuses_count from users where user_id=" + userId;
			System.out.println(sql);
			con = DatabaseDao.getConnection();
			stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			rs.next();
			result = rs.getInt(1);
		} catch (Exception e) {
			e.printStackTrace();
		}

		return result;
	}

	/**
	 * @author 韩 闻文 get user's comment count
	 * @param userId
	 * @return
	 */
	public static int getCommentCountByUserId(String userId) {
		int result = 0;
		Statement stmt = null;
		try {
			String sql = "select count(*) from comments where user_id=" + userId;
			System.out.println(sql);
			con = DatabaseDao.getConnection();
			stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			rs.next();
			result = rs.getInt(1);
		} catch (Exception e) {
			e.printStackTrace();
		}

		return result;
	}

	/**
	 * @author 韩 闻文 get user's follower count
	 * @param userId
	 * @return
	 */
	public static int getFollowerCountByUserId(String userId) {
		int result = 0;
		Statement stmt = null;
		try {
			String sql = "select followers_count from users where user_id='" + userId + "'";
			con = DatabaseDao.getConnection();
			stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			rs.next();
			result = rs.getInt(1);
		} catch (Exception e) {
			e.printStackTrace();
		}

		return result;
	}

	/**
	 * @author 韩 闻文 get user's friend count
	 * @param userId
	 * @return
	 */
	public static int getFriendCountByUserId(String userId) {
		int result = 0;
		Statement stmt = null;
		try {
			String sql = "select friends_count from users where user_id=" + userId;
			System.out.println(sql);
			con = DatabaseDao.getConnection();
			stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			rs.next();
			result = rs.getInt(1);
		} catch (Exception e) {
			e.printStackTrace();
		}

		return result;
	}

	/**
	 * @author 韩 闻文 get user's average comment length, unit is "character"
	 * @param userId
	 * @return
	 */
	public static double getAVGCommentLength(String userId) {
		double result = 0;
		double sum = 0.0;
		int count = 0;
		Statement stmt = null;
		try {
			String sql = "select content from comments where user_id=" + userId;
			System.out.println(sql);
			con = DatabaseDao.getConnection();
			stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				int length = rs.getString(1).length();
				sum += length;
				System.out.println("length:" + length);
				count++;
			}
			if (count != 0)
				result = sum / count;
			else
				result = -1;// -1 represent the user has no statuses,,or the
							// user are not existed
		} catch (Exception e) {
			e.printStackTrace();
		}

		return result;
	}

	/**
	 * @author 韩 闻文 get user's average comment length, unit is "character"
	 * @param userId
	 * @return
	 */
	public static double getAVGStatusLength(String userId) {
		double result = 0;
		double sum = 0.0;
		int count = 0;
		Statement stmt = null;
		try {
			String sql = "select content from statuses where user_id=" + userId;
			con = DatabaseDao.getConnection();
			stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				int length = rs.getString(1).length();
				sum += length;
				count++;
				System.out.println("length:" + length);
			}
			System.out.println("sum,count:" + sum + "," + count);
			if (count != 0)
				result = sum / count;
			else
				result = -1;// -1 represent the user has no statuses,or the user
							// are not existed
		} catch (Exception e) {
			e.printStackTrace();
		}

		return result;
	}

	public static int getRetweetCountByUserId(String userId) {
		ArrayList<Status> statuses = StatusService.getStatusesByUserId(userId);
		System.out.println("status count:" + statuses.size());
		int sum = 0;
		for (int i = 0; i < statuses.size(); i++) {
			String statusId = statuses.get(i).getStatusId();
			int count = StatusDao.getRetweetedCountById(statusId);
			sum += count;
		}
		return sum;
	}

	public static User getById(String userId) {
		User user = new User();
		try {
			String sql = "select * from users where user_id=" + userId;
			// System.out.println(sql);
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				user.setUserId(rs.getString("user_id"));
				user.setName(rs.getString("name"));
				user.setScreenName(rs.getString("screen_name"));
				user.setDescription(rs.getString("description"));
				user.setGender(rs.getString("gender"));
				user.setProvince(rs.getString("province"));
				user.setCity(rs.getString("city"));
				user.setLocation(rs.getString("location"));
				user.setFriendCount(rs.getInt("friends_count"));
				user.setFollowsCount(rs.getInt("followers_count"));
				user.setStatusesCount(rs.getInt("statuses_count"));
				user.setCreatedAt(rs.getString("created_at"));
				user.setUpdatedAt(rs.getString("update_time"));
				user.setVerified(rs.getInt("verified"));
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return user;
	}

	// 统计频度分布，个数是10为单位
	public static void insertFVSFF(String entity) {
		Hashtable<Integer, Integer> F_FF = new Hashtable<Integer, Integer>();
		try {
			String sql = "select " + entity + "_count from users";
			Statement stmt = DatabaseDao.getConnection().createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				int key = rs.getInt(entity + "_count") / 1000;
				if (F_FF.containsKey(key))
					F_FF.put(key, F_FF.get(key) + 1);
				else
					F_FF.put(key, 1);

			}

			Enumeration en = F_FF.keys();
			while (en.hasMoreElements()) {
				Integer key = (Integer) en.nextElement();
				Integer value = F_FF.get(key);
				sql = "insert into " + entity + "_distribution values('" + key + "'," + value + ")";
				DatabaseDao.updateData(sql);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public static void main(String args[]) {
		// UserDao.getAvgCommentWordCount("1886148044");
		double avgCommentLength = getAVGCommentLength("2709577332");
		System.out.println(avgCommentLength);
		double avgStatusLength = getAVGStatusLength("1700648435");// 1498325391
		System.out.println(avgStatusLength);
	}
}
